package org.csu.mypetstore.persistence;

import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.csu.mypetstore.domain.Item;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface ItemMapper {
    @Update("        UPDATE INVENTORY SET\n" +
            "            QTY = QTY - #{increment}\n" +
            "        WHERE ITEMID = #{itemId}")
    void updateInventoryQuantity(Map<String, Object> param);

    @Select("        SELECT QTY AS value\n" +
            "        FROM INVENTORY\n" +
            "        WHERE ITEMID = #{itemId}")
    int getInventoryQuantity(String itemId);

    @Select("        SELECT\n" +
            "            I.ITEMID,\n" +
            "            LISTPRICE,\n" +
            "            UNITCOST,\n" +
            "            SUPPLIER AS supplierId,\n" +
            "            I.PRODUCTID AS \"product.productId\",\n" +
            "            NAME AS \"product.name\",\n" +
            "            DESCN AS \"product.description\",\n" +
            "            IMAGE as \"product.image\",\n" +
            "            CATEGORY AS \"product.categoryId\",\n" +
            "            STATUS,\n" +
            "            ATTR1 AS attribute1,\n" +
            "            ATTR2 AS attribute2,\n" +
            "            ATTR3 AS attribute3,\n" +
            "            ATTR4 AS attribute4,\n" +
            "            ATTR5 AS attribute5\n" +
            "        FROM ITEM I, PRODUCT P\n" +
            "        WHERE P.PRODUCTID = I.PRODUCTID\n" +
            "          AND I.PRODUCTID = #{itemId}")
    List<Item> getItemListByProduct(String productId);

    @Select("        select\n" +
            "            I.ITEMID,\n" +
            "            LISTPRICE,\n" +
            "            UNITCOST,\n" +
            "            SUPPLIER AS supplierId,\n" +
            "            I.PRODUCTID AS \"product.productId\",\n" +
            "            NAME AS \"product.name\",\n" +
            "            IMAGE AS \"product.description\",\n" +
            "            CATEGORY AS \"product.categoryId\",\n" +
            "            STATUS,\n" +
            "            ATTR1 AS attribute1,\n" +
            "            ATTR2 AS attribute2,\n" +
            "            ATTR3 AS attribute3,\n" +
            "            ATTR4 AS attribute4,\n" +
            "            ATTR5 AS attribute5,\n" +
            "            QTY AS quantity\n" +
            "        from ITEM I, INVENTORY V, PRODUCT P\n" +
            "        where P.PRODUCTID = I.PRODUCTID\n" +
            "          and I.ITEMID = V.ITEMID\n" +
            "          and I.ITEMID = #{itemId}")
    Item getItem(String itemId);

    @Select("        select\n" +
            "            *,\n" +
            "            SUPPLIER AS supplierId,\n" +
            "            ATTR1 AS attribute1,\n" +
            "            ATTR2 AS attribute2,\n" +
            "            ATTR3 AS attribute3,\n" +
            "            ATTR4 AS attribute4,\n" +
            "            ATTR5 AS attribute5\n" +
            "        from ITEM")
    List <Item>getAllItem();

    @Select("        select\n" +
            "            *,\n" +
            "            SUPPLIER AS supplierId,\n" +
            "            ATTR1 AS attribute1,\n" +
            "            ATTR2 AS attribute2,\n" +
            "            ATTR3 AS attribute3,\n" +
            "            ATTR4 AS attribute4,\n" +
            "            ATTR5 AS attribute5\n" +
            "        from ITEM\n" +
            "        where itemid=#{value}")
    Item getItemByItemID(String itemId);
}
